IS [NOT] JSON

Usage Notes

• A valid JSON string is either a JSON array, a JSON object, or a JSON scalar. In each case, it has to be valid according to the syntax rules on JSON.org.
• IS JSON is equivalent to IS JSON VALUE and returns true for a valid JSON array, a valid JSON object, or a valid JSON scalar.
• IS JSON ARRAY is only true if the input expression is a valid JSON array according to the syntax rules on JSON.org.
• IS JSON OBJECT is only true if the input expression is a valid JSON object according to the syntax rules on JSON.org.
• IS JSON SCALAR is only true if the input expression is a valid JSON scalar according to the syntax rules on JSON.org.
• A JSON object may have non-unique keys. For example in the JSON string '{"name" : "Ann", "name" : "John"}', 'name' is a non-unique key.
• If WITH UNIQUE [KEYS] is set, IS JSON returns false for valid JSON objects with non-unique keys. WITHOUT UNIQUE [KEYS] is the default.

Example

CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, NULL);
INSERT INTO json_input VALUES(2, '{"name" : "John"}');
INSERT INTO json_input VALUES(3, '{"name" : {"forename" : "John", "name" : "Doe"}}');
INSERT INTO json_input VALUES(4, '{"name" : "John", "name" : "Ann"}');
INSERT INTO json_input VALUES(5, '[{"name" : "John"}]');
INSERT INTO json_input VALUES(6, 'true');
INSERT INTO json_input VALUES(7, 'false');
INSERT INTO json_input VALUES(8, 'null');
INSERT INTO json_input VALUES(9, '42');
INSERT INTO json_input VALUES(10, '1.0');
INSERT INTO json_input VALUES(11, '"Exasol"');
INSERT INTO json_input VALUES(12, '{name : "John"}');

SELECT id, json, json IS JSON as IS_JSON, json IS NOT JSON as IS_NOT_JSON,
       json IS JSON WITH UNIQUE KEYS as IS_JSON_WITH_UNIQUE_KEYS,
       json IS JSON VALUE as "JSON VALUE", json IS JSON ARRAY as JSON_ARRAY,
       json IS JSON OBJECT as JSON_OBJECT, json IS JSON SCALAR as JSON_SCALAR
FROM json_input
ORDER BY id;

Results